from io import BytesIO
from flask import Flask, render_template, request, redirect, url_for, flash, send_file
from flask_sqlalchemy import SQLAlchemy
import string
import random
from datetime import datetime
import io
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key-here'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@localhost/inventory_db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


# 物品模型
class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    item_code = db.Column(db.String(20), unique=True, nullable=False)  # 物品编号
    name = db.Column(db.String(100), nullable=False)  # 物品名称
    specification = db.Column(db.String(100))  # 规格型号
    material = db.Column(db.String(100))  # 材质
    unit = db.Column(db.String(20), nullable=False)  # 单位
    price = db.Column(db.Float, nullable=False)  # 单价
    quantity = db.Column(db.Integer, default=0)  # 数量
    total_value = db.Column(db.Float, default=0)  # 总价值
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'item_code': self.item_code,
            'name': self.name,
            'specification': self.specification,
            'material': self.material,
            'unit': self.unit,
            'price': self.price,
            'quantity': self.quantity,
            'total_value': self.total_value,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M')
        }


# 生成随机物品编号
def generate_item_code():
    prefix = 'ITM'
    chars = string.ascii_uppercase + string.digits
    code = prefix + ''.join(random.choice(chars) for _ in range(6))

    # 检查编号是否已存在
    if Item.query.filter_by(item_code=code).first():
        return generate_item_code()
    return code
class OutboundRecord(db.Model):
    """出库记录模型"""
    id = db.Column(db.Integer, primary_key=True)
    item_code = db.Column(db.String(20), nullable=False, comment='物品编号')
    name = db.Column(db.String(100), nullable=False, comment='物品名称')
    specification = db.Column(db.String(100), nullable=True, comment='规格型号')  # 优化点一新增
    material = db.Column(db.String(100), nullable=True, comment='材质')  # 优化点一新增
    unit = db.Column(db.String(20), nullable=False, comment='单位')
    price = db.Column(db.Float, nullable=False, comment='单价')
    quantity = db.Column(db.Integer, nullable=False, comment='出库数量')
    total_value = db.Column(db.Float, nullable=False, comment='总价值')
    customer = db.Column(db.String(100), nullable=False, comment='客户单位')  # 优化点三新增
    created_at = db.Column(db.DateTime, default=datetime.now, comment='出库时间')

    def to_dict(self):
        return {
            'id': self.id,
            'item_code': self.item_code,
            'name': self.name,
            'specification': self.specification,
            'material': self.material,
            'unit': self.unit,
            'price': self.price,
            'quantity': self.quantity,
            'total_value': self.total_value,
            'customer': self.customer,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M')
        }
# 首页路由
@app.route('/')
def index():
    return render_template('base.html')


# 入库操作路由
@app.route('/stock-in', methods=['GET', 'POST'])
def stock_in():
    if request.method == 'POST':
        name = request.form['name']
        specification = request.form['specification']
        material = request.form['material']
        unit = request.form['unit']
        price = float(request.form['price'])
        quantity = int(request.form['quantity'])
        total_value = price * quantity

        # 检查物品是否已存在
        existing_item = Item.query.filter_by(name=name, specification=specification, material=material).first()

        if existing_item:
            # 更新现有物品
            existing_item.quantity += quantity
            existing_item.total_value += total_value
            existing_item.updated_at = datetime.utcnow()
            db.session.commit()
            flash(f'物品 "{name}" 入库成功！当前库存: {existing_item.quantity}{unit}', 'success')
        else:
            # 创建新物品
            item_code = generate_item_code()
            new_item = Item(
                item_code=item_code,
                name=name,
                specification=specification,
                material=material,
                unit=unit,
                price=price,
                quantity=quantity,
                total_value=total_value
            )
            db.session.add(new_item)
            db.session.commit()
            flash(f'新物品 "{name}" 入库成功！物品编号: {item_code}', 'success')

        return redirect(url_for('stock_in'))

    return render_template('stock_in.html')


# 库存查询路由
@app.route('/inventory', methods=['GET'])
def inventory():
    search_query = request.args.get('search', '')
    if search_query:
        items = Item.query.filter(Item.name.like(f'%{search_query}%')).order_by(Item.name).all()
    else:
        items = Item.query.order_by(Item.name).all()
    return render_template('inventory.html', items=items, search_query=search_query)


# 出库操作路由
@app.route('/stock-out', methods=['GET', 'POST'])
def stock_out():
    if request.method == 'POST':
        if 'generate_excel' in request.form:
            # 生成Excel出库清单
            record_ids = request.form.getlist('record_ids')
            if not record_ids:
                flash('请至少选择一条记录', 'warning')
                return redirect(url_for('stock_out'))

            # 获取选中的记录
            selected_records = OutboundRecord.query.filter(
                OutboundRecord.id.in_(record_ids)
            ).all()

            if not selected_records:
                flash('未找到选中的记录', 'danger')
                return redirect(url_for('stock_out'))

            # 按客户单位分组
            from collections import defaultdict
            customer_groups = defaultdict(list)
            for record in selected_records:
                customer_groups[record.customer].append(record)

            # 创建Excel
            wb = Workbook()

            # 遍历每个客户单位，创建单独的sheet
            for idx, (customer, records) in enumerate(customer_groups.items(), 1):
                # 创建新sheet（第一个客户使用默认sheet，后续客户新建）
                if idx == 1:
                    ws = wb.active
                    ws.title = customer  # 重命名默认sheet
                else:
                    ws = wb.create_sheet(title=customer)  # 新sheet以客户单位命名

                ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=9)  # 合并 1-9 列
                title_cell = ws.cell(row=1, column=1)
                title_cell.value = "出库清单"
                title_cell.font = Font(bold=True,size=14)
                title_cell.alignment = Alignment(horizontal='center', vertical='center')

                # 设置表头
                headers = [
                    '序号','物品编号', '物品名称', '规格型号', '材质',
                    '单位', '单价 (元)', '出库数量', '总价值 (元)'
                ]
                for col, header in enumerate(headers, 1):
                    cell = ws.cell(row=2, column=col)
                    cell.value = header
                    cell.font = Font(bold=True)
                    cell.alignment = Alignment(horizontal='center')
                    cell.border = Border(
                        left=Side(style='thin'),
                        right=Side(style='thin'),
                        top=Side(style='thin'),
                        bottom=Side(style='thin')
                    )

                # 填充数据
                total_sum = 0  # 每个客户的总价值合计
                for row_, record in enumerate(records, 2):
                    row=row_+1
                    ws.cell(row=row, column=1).value = row-2
                    ws.cell(row=row, column=2).value = record.item_code
                    ws.cell(row=row, column=3).value = record.name
                    ws.cell(row=row, column=4).value = record.specification or ''
                    ws.cell(row=row, column=5).value = record.material or ''
                    ws.cell(row=row, column=6).value = record.unit
                    ws.cell(row=row, column=7).value = record.price
                    ws.cell(row=row, column=7).number_format = '0.00'
                    ws.cell(row=row, column=8).value = record.quantity
                    ws.cell(row=row, column=9).value = record.total_value
                    ws.cell(row=row, column=9).number_format = '0.00'

                    # 计算客户的总价值
                    total_sum += record.total_value

                    # 添加边框
                    for col in range(1, 10):
                        ws.cell(row=row, column=col).border = Border(
                            left=Side(style='thin'),
                            right=Side(style='thin'),
                            top=Side(style='thin'),
                            bottom=Side(style='thin')
                        )

                # 添加客户的合计行
                total_row = len(records) + 3
                ws.cell(row=total_row, column=8).value = '合计：'
                ws.cell(row=total_row, column=8).font = Font(bold=True)
                ws.cell(row=total_row+1, column=8).value = f'客户单位：{customer}'
                ws.cell(row=total_row+1, column=8).font = Font(bold=True)
                ws.cell(row=total_row, column=9).value = total_sum
                ws.cell(row=total_row, column=9).font = Font(bold=True)
                ws.cell(row=total_row, column=9).number_format = '0.00'

                # 调整列宽
                ws.column_dimensions['A'].width = 12
                ws.column_dimensions['B'].width = 15
                ws.column_dimensions['C'].width = 15
                ws.column_dimensions['D'].width = 10
                ws.column_dimensions['E'].width = 8
                ws.column_dimensions['F'].width = 10
                ws.column_dimensions['G'].width = 10
                ws.column_dimensions['H'].width = 12
                ws.column_dimensions['I'].width = 12

            # 保存到内存并下载
            buffer = BytesIO()
            wb.save(buffer)
            buffer.seek(0)

            # 生成文件名
            filename = f'出库清单_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx'
            return send_file(
                buffer,
                as_attachment=True,
                download_name=filename,
                mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
        # 处理出库操作
        item_code = request.form['item_code']
        quantity = int(request.form['quantity'])
        customer = request.form['customer']

        if not customer:
            flash('客户单位为必填项，请填写', 'danger')
            return redirect(url_for('stock_out'))

        item = Item.query.filter_by(item_code=item_code).first()

        if not item:
            flash(f'物品编号 {item_code} 不存在', 'danger')
            return redirect(url_for('stock_out'))

        if item.quantity < quantity:
            flash(f'库存不足！当前库存: {item.quantity}{item.unit}', 'danger')
            return redirect(url_for('stock_out'))

        # 更新库存
        item.quantity -= quantity
        item.total_value = item.price * item.quantity
        item.updated_at = datetime.utcnow()

        # 创建出库记录
        out_record = OutboundRecord(
            item_code=item.item_code,
            name=item.name,
            specification=item.specification,
            material=item.material,
            unit=item.unit,
            price=item.price,
            quantity=quantity,
            total_value=item.price * quantity,
            customer=customer
        )

        db.session.add(out_record)
        db.session.commit()

        flash(f'物品 "{item.name}" 出库成功！出库数量: {quantity}{item.unit}', 'success')
        return redirect(url_for('stock_out'))

    # 获取所有物品和出库记录
    items = Item.query.all()
    # 将模型对象转换为字典列表（使用我们定义的 to_dict() 方法）
    items_dict = [item.to_dict() for item in items]  # 关键修改

    # 获取所有出库记录（按时间倒序）
    out_records = OutboundRecord.query.order_by(OutboundRecord.created_at.desc()).all()
    return render_template('stock_out.html', items=items_dict, out_records=out_records)  # 关键修改


# 创建数据库表
with app.app_context():
    db.create_all()

if __name__ == '__main__':
    app.run(debug=True)